<?
	session_start();
	include('conn.php');
	//error_reporting(E_ALL);
	$q = $_REQUEST["q"];
	switch ($q)
	{
		case('1'):
		{
			$qry1 = "SELECT * from society";		
			$qry = mysql_query($qry1);
			$data = array();

			while($rows = mysql_fetch_array($qry))
			{
				$data[] = $rows['societyname'];
				/*$data[] = array
				(						
					"prefix" => $rows['societyprefix'],
					"name" => $rows['societyname']
				);*/
			}
			echo json_encode($data);
			break;
		}
		case('2'):
		{
				$soc = $_REQUEST["soc"];
				$qry1 = "select socid from society where societyname='$soc';";			
				$res1 = mysql_query($qry1);
				$row = mysql_fetch_array($res1);
				$sid = $row["socid"];
							
				//$soc = explode(" - ", $_REQUEST["soc"]);			
				$qrysel = "SELECT RoomNumber from Customer where socId ='$sid';"; 			
				$ressel = mysql_query($qrysel);				
				while($rows = mysql_fetch_array($ressel))
				{		
					$data[] = array
					(			
						"room" => $rows['RoomNumber']					
					);
				}
			echo json_encode($data);
			break;
		}
		case('3'):
		{
			$totalamt = 0;
			$soc = $_REQUEST["soc"];
			$room = $_REQUEST["room"];
					
			$qry1 = "select socid from society where societyname='$soc';";			
			$res1 = mysql_query($qry1);
			$row1 = mysql_fetch_array($res1);
			$sid = $row1["socid"];
			
			$qry2 = "select CustId from Customer where socId='$sid' and RoomNumber='$room';"; 			
			$res2 = mysql_query($qry2);
			$row2 = mysql_fetch_array($res2);
			$cid = $row2["CustId"];
			
			$qry3= "SELECT stbtocustid from STBtoCustomer where CustId='$cid'";			
			$res3 = mysql_query($qry3);
			
			do
			{			
				$scid = $row3['stbtocustid'];
				if (!is_null ($scid)){					
					$ext1 = " ,sc.RateperMonth,	sc.ActivationDate, sc.TerminationDate, c.Notes,
					
					(select sum(p1.`payamount`) from payments p1 where p1.`CustId` = '$cid' ) as tsum,
					
					((period_diff(date_format((case when isnull(sc.`TerminationDate`) then now() 
					when ( sc.`TerminationDate` = '0000-00-00 00:00:00') then now() 
					else  sc.`TerminationDate` end),'%Y%m'),
					date_format(sc.`ActivationDate`,'%Y%m')) + 1) *  sc.`RateperMonth`) AS `totalout`";
					
					$ext2 = " and sc.stbtocustid='$scid'";
				}
				
				$qrysel_soc = "SELECT c.CustomerName, c.TelNo $ext1					
					from Customer c,STBtoCustomer sc where c.CustId='$cid' $ext2"; 
					//echo "<br>".$qrysel_soc."<br><br>";
					
					$ressel_soc = mysql_query($qrysel_soc);	
					while($rows = mysql_fetch_array($ressel_soc))
					{	
						$totalamt += $rows['totalout'];
						$bal = $totalamt - $rows['tsum'];
						$notevar = '';						
						if(is_null($scid))
							$notevar = "Customer not mapped to any STB";
							
						$data = array
						(															
							"cname" => $rows['CustomerName'],											
							"phone" => $rows['TelNo'],
							"rpm" => $rows['RateperMonth'],
							"actdt" => $rows['ActivationDate'],
							"termdt" => $rows['TerminationDate'],
							"note" => $notevar,
							"balance" => $bal							
						);					
					}					
			}while($row3 = mysql_fetch_array($res3));
			echo json_encode($data);
			BREAK;
		}
		case('4'):
		{									
			$qrysel = "SELECT * from payments order by payid desc";		
			$qry = mysql_query($qrysel);
			$data = array();

			while($rows = mysql_fetch_array($qry))
			{
				$cid = $rows['CustId'];
				$qry1 = "select c.CustomerName,
				c.RateperMonth,
				(select sum(p1.`payamount`) from payments p1 where p1.`CustId` = '$cid' ) as tsum,
				((period_diff(date_format((case when isnull(c.`dateoftermination`) then now() when ( c.`dateoftermination` = '0000-00-00 00:00:00') then now() else  c.`dateoftermination` end),'%Y%m'),date_format(c.`dateofactivation`,'%Y%m')) + 1) *  c.`RateperMonth`) AS `totalout`
				from Customer c,payments p where c.CustId='$cid' and p.CustId='$cid';";
				$row1=mysql_fetch_array(mysql_query($qry1));
				$cname = $row1['CustomerName'];
				$rpm = $row1['RateperMonth'];
				$totaloutamt = $row1['totalout'];
				$totalpaidamt = $row1['tsum'];
			
				$sid = $rows['socId'];
				$qry2 = "select societyname from society where socid='$sid' ;";
				$row2=mysql_fetch_array(mysql_query($qry2));
				$sname = $row2['societyname'];
				
				$data[] = array
				(														
					"id" => $rows['payid'],
					"cname" => $cname,
					"sname" => $sname,
					"rpm" => $rpm,
					"t_out" => $totaloutamt,
					"t_paid" => $totalpaidamt,
					"balance" => $totaloutamt - $totalpaidamt,
					"paydate" => $rows['paydate'],
					"payamount" => $rows['payamount'],
					"notes" => $rows['notes'],
					"RoomNumber" => $rows['RoomNumber'],
					"TelNo" => $rows['TelNo']
					//,"balance" => $rows['balance']
				);
			}
			echo json_encode($data);
			break;
		}
		default:
		{
			echo "No option selected";
			break;
		}
	}//end switch
	
	
?>